﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Spring.Data.Generic;
using System.Data;
using Spring.Data.Common;
using System.Data.SqlClient;

namespace HotelApp
{
    public static class NewsInfoDAO
    {
        #region static field

        private static AdoTemplate template = AppConfigurer.AdoTemplate;
        private static Object lockObj = new Object();
        private static string strBase = "select News_Code, Category_Code, News_Name, News_Desc,"
            + " News_Content, Creator, Create_Date from News_Info";

        #endregion

        #region internal methods

        internal static void CreateNewsInfo(NewsInfo obj)
        {
            //Check input parameter
            if (obj == null)
                throw new Exception("NewsInfoDAO.CreateNewsInfo: Invalid parameter");

            lock (lockObj)
            {
                //Generate ItemCode
                string strSearch = "SELECT isnull(max(News_Code),'N000000000') FROM News_Info ";
                string strMaxNo = template.ExecuteScalar(CommandType.Text, strSearch).ToString();
                int nNum = Int32.Parse(strMaxNo.Substring(1)) + 1;
                obj.NewsCode = "N" + nNum.ToString("000000000");

                //Insert into table News_Info
                string strCreate = "insert into News_Info values(@News_Code, @Category_Code, @News_Name, @News_Desc,"
                + " @News_Content, @Creator, @Create_Date)";

                IDbParameters para = template.CreateDbParameters();
                para.Add("News_Code", DbType.String).Value = obj.NewsCode;
                para.Add("Category_Code", DbType.String).Value = obj.CategoryCode;
                para.Add("News_Name", DbType.String).Value = obj.NewsName;
                para.Add("News_Desc", DbType.String).Value = obj.NewsDesc == null ? string.Empty : obj.NewsDesc;
                para.Add("News_Content", DbType.String).Value = obj.NewsContent;
                para.Add("Creator", DbType.String).Value = obj.Creator;
                para.Add("Create_Date", DbType.DateTime).Value = obj.CreateDate;

                template.ExecuteNonQuery(CommandType.Text, strCreate, para);
            }
        }

        internal static void UpdateNewsInfo(NewsInfo obj)
        {
            //Check input parameter
            if (obj == null)
                throw new Exception("NewsInfoDAO.UpdateNewsInfo: Invalid parameter");

            string strSql = "update News_Info set News_Name=@News_Name,News_Desc=@News_Desc,"
                + " News_Content=@News_Content where News_Code=@News_Code";

            IDbParameters para = template.CreateDbParameters();
            para.Add("News_Code", DbType.String).Value = obj.NewsCode;
            para.Add("News_Name", DbType.String).Value = obj.NewsName;
            para.Add("News_Desc", DbType.String).Value = obj.NewsDesc == null ? string.Empty : obj.NewsDesc;
            para.Add("News_Content", DbType.String).Value = obj.NewsContent;

            template.ExecuteNonQuery(CommandType.Text, strSql, para);
        }

        internal static void DeleteNewsInfo(string newsCode)
        {
            string strSql = "delete from News_Info where News_Code=@News_Code";
            IDbParameters para = template.CreateDbParameters();
            para.Add("News_Code", DbType.String).Value = newsCode;

            template.ExecuteNonQuery(CommandType.Text, strSql, para);
        }

        #endregion

        #region public methods

        public static NewsInfo GetNewsInfo(string newsCode)
        {
            string strSql = strBase + " where News_Code=@News_Code";

            IDbParameters para = template.CreateDbParameters();
            para.Add("News_Code", DbType.String).Value = newsCode;

            IList<NewsInfo> lst = template.QueryWithRowMapperDelegate(CommandType.Text, strSql,
                new RowMapperDelegate<NewsInfo>(MapRow), para);

            return lst.Count > 0 ? lst[0] : null;
        }

        public static IList<NewsInfo> ListNewsInfos(string categoryCode, int startIndex, int limit, ref int totalCount)
        {
            //string strSql = strBase + " where Category_Code=@Category_Code";

            IList<NewsInfo> lst = new List<NewsInfo>();

            IDbParameters para = template.CreateDbParameters();
            para.Add("CategoryCode", DbType.String).Value = categoryCode;
            para.Add("pageIndex", DbType.Int32).Value = startIndex;
            para.Add("pageSize", DbType.Int32).Value = limit;
            para.AddOut("totalCount", DbType.Int32);

            lst = template.QueryWithRowMapperDelegate(CommandType.StoredProcedure, "proc_paged_with_Rownumber",
                new RowMapperDelegate<NewsInfo>(MapRow), para);

            totalCount = int.Parse(((SqlParameter)(para.GetValue("totalCount"))).Value.ToString());
            
            return lst;
        }

        #endregion

        #region private methods

        private static NewsInfo MapRow(IDataReader dr, int rownumber)
        {
            NewsInfo obj = new NewsInfo();
            obj.NewsCode = dr.IsDBNull(0) ? "" : dr.GetString(0);
            obj.CategoryCode = dr.IsDBNull(1) ? "" : dr.GetString(1);
            obj.NewsName = dr.IsDBNull(2) ? "" : dr.GetString(2);
            obj.NewsDesc = dr.IsDBNull(3) ? "" : dr.GetString(3);
            obj.NewsContent = dr.IsDBNull(4) ? "" : dr.GetString(4);
            obj.Creator = dr.IsDBNull(5) ? "" : dr.GetString(5);
            obj.CreateDate = dr.IsDBNull(6) ? DateTime.MinValue : dr.GetDateTime(6);

            return obj;
        }

        #endregion
    }
}
